#!/usr/bin/python
# -*- coding: utf-8 -*-
# ########################################################################
# # This scaffolding model makes your app work on Google App Engine too
# ########################################################################

try:
    from gluon.contrib.gql import *  # if running on Google App Engine
except:
    db = SQLDB('sqlite://storage.db')  # if not, use SQLite or other DB
else:
    db = GQLDB()  # connect to Google BigTable
    session.connect(request, response, db=db)  # and store sessions there

# ########################################################################
# # uncomment the following line if you do not want sessions
# session.forget()
# ########################################################################

# ########################################################################
# # Define your tables below, for example
##
from gluon.tools import Auth
## instantiate the Auth class (or your derived class)
auth = Auth(globals(), db)
##create all necessary tables
auth.define_tables()

#import datetime; now=datetime.datetime.today()

db.define_table('category', 
				SQLField('name'), 
				SQLField('sort_order', 'integer')
				)

db.define_table('status', 
				SQLField('name'), 
				SQLField('sort_order', 'integer'))

db.define_table('priority', 
				SQLField('name'), 
				SQLField('sort_order', 'integer'))

#not using 'case' as it is a reserved word
db.define_table('cases',
    SQLField('title'),
    SQLField('description', 'text', length=256),
    SQLField('created_via'),
#    SQLField('category', db.category),
    SQLField('status', db.status),
    SQLField('priority', db.priority),
    SQLField('assigned_to', db.auth_user), #it should actually be agents
    SQLField('created_by', db.auth_user),    
    SQLField('created_time', 'datetime'),
    SQLField('last_modified_by', db.auth_user),
    SQLField('last_modified_time', 'datetime')
    )

db.define_table('notes',
	SQLField('case_id', db.cases),
	SQLField('author', db.auth_user),
	SQLField('body', 'text', length=256),
	SQLField('created_via'),
	SQLField('created_time', 'datetime')
	)
	
db.define_table('config',
	SQLField('twitter_id'),
	SQLField('twitter_pwd'),
	SQLField('last_twitter_intime', 'datetime'),
	SQLField('last_dm_id','integer')
	)
	

db.cases.priority.requires=IS_IN_DB(db, 'priority.id','priority.name')
db.cases.created_by.required=IS_IN_DB(db, 'auth_user.id')
db.cases.title.requires=[IS_NOT_EMPTY()]
db.cases.description.requires=[IS_NOT_EMPTY()]
db.notes.body.requires=IS_NOT_EMPTY()

#db.priority.name.requires=[IS_NOT_EMPTY(), IS_NOT_IN_DB(db, 'priority.name')]

##insert dummy records##
if len(db().select(db.priority.ALL)) == 0:
    db.priority.insert(name='Low', sort_order=1)
    db.priority.insert(name='Medium', sort_order=2)
    db.priority.insert(name='High', sort_order=3)

if len(db().select(db.status.ALL)) == 0:
    db.status.insert(name='New', sort_order=1)
    db.status.insert(name='Assigned', sort_order=2)
    db.status.insert(name='Open', sort_order=3)
    db.status.insert(name='Work In Progress', sort_order=4)
    db.status.insert(name='Closed', sort_order=5)
    
#if len(db().select(db.category.ALL)) == 0:
#    db.category.insert(name='Generic Questions')
#    db.category.insert(name='Pricing')

if len(db().select(db.auth_group.ALL)) == 0:
    db.auth_group.insert(role='admin')
    db.auth_group.insert(role='agents')

from gluon.utils import md5_hash
if len(db().select(db.auth_user.ALL)) == 0:
    db.auth_user.insert(first_name='admin', last_name='1', email='admin@example.com', password=md5_hash('web2py'))
    db.auth_user.insert(first_name='agent1', last_name='1', email='agent1@example.com', password=md5_hash('web2py'))
    db.auth_user.insert(first_name='agent2', last_name='1', email='agent2@example.com', password=md5_hash('web2py'))
    db.auth_user.insert(first_name='user', last_name='1', email='user@example.com', password=md5_hash('web2py'))

if len(db().select(db.auth_membership.ALL)) == 0:
    db.auth_membership.insert(user_id=db(db.auth_user.first_name=='admin').select(db.auth_user.ALL)[0].id, group_id=db(db.auth_group.role=='admin').select(db.auth_group.id)[0].id)
    db.auth_membership.insert(user_id=db(db.auth_user.first_name=='agent1').select(db.auth_user.ALL)[0].id, group_id=db(db.auth_group.role=='agents').select(db.auth_group.id)[0].id)
    db.auth_membership.insert(user_id=db(db.auth_user.first_name=='agent2').select(db.auth_user.ALL)[0].id, group_id=db(db.auth_group.role=='agents').select(db.auth_group.id)[0].id)
    
if len(db().select(db.config.ALL))==0:
	import datetime
	db.config.insert(twitter_id='yatsy', twitter_pwd='changepwd', last_twitter_intime=datetime.datetime.now(), last_dm_id=0)